package hezi.helper;

import hezi.core.Site;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.log4j.Logger;

import dz.common.DBHelper;

public class SiteDBHelper {

	static Logger logger = Logger.getLogger("");

	static String dbfilepath = ForumDBHelper.getDbfilepath();
	
	static Connection conn = DBHelper.getConnection(dbfilepath);
	public static void staticinit(){
		createTableIfNotExist(conn);
	}
	
	

	public static void createTableIfNotExist(Connection conn) {
		try {
			Statement stat = conn.createStatement();
			stat.executeUpdate("create table if not exists site (" +
					"siteid char(10), " +
					"sitename char(10)," +
					"domain varchar(50)," +
					"urlp varchar(20)," +//1
					"trp varchar(20)," +
					"titlep varchar(20)," +
					"contentp varchar(20)," +
					"urlkeyword varchar(20)," +
					"loginparas varchar(20),"+
					"loginurl varchar(20),"+
					"loginurl sitetype(10)"+
					");");
			stat.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	public synchronized static void insert(Site site) {
		logger.info("insert site:" + site.getId());
		String sqlstr = "insert into site values("+"?,?,?,?,?,"+"?,?,?,?,?,"+"?)";
		
		try {
			PreparedStatement prep = conn.prepareStatement(sqlstr);
			
			prep.setString(1,site.siteid);
			prep.setString(2,site.sitename);
			prep.setString(3,site.domain);
			prep.setString(4,site.urlp);
			
			
			prep.setString(5,site.trp);
			prep.setString(6,site.titlep);
			prep.setString(7,site.contentp);
			prep.setString(8,site.urlkeyword);
			
			prep.setString(9,site.loginparas);
			prep.setString(10,site.loginurl);
			prep.setString(11,site.sitetype);
			

			prep.executeUpdate();
			
			prep.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	



	public static void select(List<Site> sites) {
		String sql =  "SELECT * FROM site";
		ResultSet rs;
		try {
			Statement stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while (rs.next()) {
				Site site = new Site(rs.getString("siteid"));
				sets(rs,site);
				sites.add(site);
			}
			rs.close();
			stat.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	
	public synchronized static void sets(ResultSet rs, Site site) throws SQLException {
		site.sitename = rs.getString("sitename");
		site.domain = rs.getString("domain");
		site.urlp =  rs.getString("urlp");
		site.trp = rs.getString("trp");
		site.titlep = rs.getString("titlep");
		site.contentp = rs.getString("contentp");
		site.urlkeyword = rs.getString("urlkeyword");
		site.loginparas = rs.getString("loginparas");
		site.loginurl = rs.getString("loginurl");
		site.sitetype = rs.getString("sitetype");
//		logger.info("==================" + site.loginurl );
	}

	
	
	
	public static Site select(String siteid) {
		Site site = null;
		String sql = "SELECT * FROM site WHERE siteid = '" + siteid + "'";
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
			while(rs.next()){
				site = new Site(siteid);
				sets(rs,site);
				break;
			}
			rs.close();
			stat.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return site;
	}

	public static void update(String siteid, String key, String newvalue) {
		String sql = "UPDATE site SET " + key + "='" + newvalue + "' WHERE siteid = '" + siteid + "'";
		logger.info(sql);
		try {
			Statement stat = conn.createStatement();
			stat.executeUpdate(sql);
			stat.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
